﻿using Common;
using Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using Dapper;

namespace DbControl
{
    public class OprRules
    {
        public static List<Rule> GetRuleAll()
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT id 
                                  ,rule_code 
                                  ,sensor_id 
                                  ,parameter_id 
                                  ,upper_upper_value 
                                  ,upper_value 
                                  ,lower_value 
                                  ,lower_lower_value 
                                  ,flag 
                              FROM rules WHERE flag>0 ";
                    return connection.Query<Rule>(sql).ToList();
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return null;
            }
        }
        public static DataTable GetRuleByCode(string Code)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = "";
                if (string.IsNullOrEmpty(Code))
                {
                    sql = @"SELECT A.id 
                                  ,A.rule_code 
                                  ,A.sensor_id 
                                  ,A.parameter_id 
                                  ,B.parameter_name
                                  ,A.upper_upper_value 
                                  ,A.upper_value 
                                  ,A.lower_value 
                                  ,A.lower_lower_value 
                                  ,A.flag 
                              FROM rules A  left join parameters B on A.parameter_id=B.id ";
                }
                else
                {
                    sql = string.Format(@"SELECT  A.id 
                                  ,A.rule_code 
                                  ,A.sensor_id 
                                  ,A.parameter_id 
                                  ,B.parameter_name
                                  ,A.upper_upper_value 
                                  ,A.upper_value 
                                  ,A.lower_value 
                                  ,A.lower_lower_value 
                                  ,A.flag 
                              FROM rules A  left join parameters B on A.parameter_id=B.id AND rule_code='{0}'", Code);
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        public static rules GetRuleById(int id)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = @"SELECT id 
                            ,rule_code 
                            ,sensor_id 
                            ,parameter_id 
                            ,upper_upper_value 
                            ,upper_value 
                            ,lower_value 
                            ,lower_lower_value 
                            ,flag 
                      FROM rules  WHERE   id=@id";
                return connection.Query<rules>(sql, new { id = id }).SingleOrDefault();
            }
        }
        public static bool IsExistRuleCode(string rule_code)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"select COUNT(id)  from rules WHERE  rule_code =@rule_code ";
                    intNum = (int)connection.ExecuteScalar(sql, new { rule_code = rule_code });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }

            return false;
        }       
        public static int DeleteById(int id)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"DELETE FROM rules WHERE id=@id";
                    sql = @"UPDATE rules SET flag = 0 WHERE id=@id";
                    return connection.Execute(sql, new { id = id });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Update(rules dv)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"UPDATE rules
                               SET rule_code= @rule_code
                                  ,sensor_id= @sensor_id
                                  ,parameter_id= @parameter_id
                                  ,upper_upper_value= @upper_upper_value
                                  ,upper_value= @upper_value
                                  ,lower_value= @lower_value
                                  ,lower_lower_value= @lower_lower_value
                                  ,flag= @flag
                                WHERE  id=@id";
                    return connection.Execute(sql, dv);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
        public static int Insert(rules r)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"INSERT INTO rules
                                   (rule_code 
                                   ,sensor_id 
                                   ,parameter_id 
                                   ,upper_upper_value 
                                   ,upper_value 
                                   ,lower_value 
                                   ,lower_lower_value 
                                   ,flag)
                             VALUES
                                   (@rule_code 
                                   ,@sensor_id 
                                   ,@parameter_id 
                                   ,@upper_upper_value 
                                   ,@upper_value 
                                   ,@lower_value 
                                   ,@lower_lower_value 
                                   ,@flag)";
                    return connection.Execute(sql, r);
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
                return 0;
            }
        }
    }
}
